﻿Imports System.Data.SqlClient
Imports Dainfo.ControleEstoque.Model


<Serializable()> _
Public MustInherit Class AcessoDadosBase
    Private _transaction As SqlTransaction
    Private _transactionConnection As SqlConnection

    Protected ReadOnly Property Conexao() As String
        Get
            Return My.Settings.ConexaoDainfoDB
        End Get
    End Property

    Protected Function GetCommand(ByVal commandText As String) As SqlCommand
        Dim cmd As New SqlCommand(commandText)
        Return cmd
    End Function

    Protected Function GetProcedureCommand(ByVal commandText As String) As SqlCommand
        Dim cmd As New SqlCommand(commandText)
        cmd.CommandType = CommandType.StoredProcedure
        Return cmd
    End Function



    Protected Sub AddParameterToCmd(ByVal cmd As SqlCommand, ByVal key As String, ByVal value As Object)

        Dim tipo As SqlDbType

        If TypeOf (value) Is Integer Then
            tipo = SqlDbType.Int
        ElseIf TypeOf (value) Is String Then
            tipo = SqlDbType.NVarChar
        ElseIf TypeOf (value) Is Double Then
            tipo = SqlDbType.Money
        ElseIf TypeOf (value) Is Boolean Then
            tipo = SqlDbType.Int
        ElseIf TypeOf (value) Is Decimal Then
            tipo = SqlDbType.Money
        ElseIf TypeOf (value) Is Date Then
            tipo = SqlDbType.Date
        End If

        AddParameterToCmd(cmd, key, value, tipo)
    End Sub



    Protected Sub AddParameterToCmd(ByVal cmd As SqlCommand, ByVal key As String, ByVal value As Object, ByVal tipo As SqlDbType)
        cmd.Parameters.Add(key, tipo, 0).Value = value
    End Sub

    Protected Sub AddParameterToCmd(ByVal cmd As SqlCommand, ByVal key As String, ByVal tipo As SqlDbType)
        cmd.Parameters.Add(key, tipo, 0)
    End Sub



    Protected Function ExecuteScalar(ByVal cmd As SqlCommand) As Integer
        Using cnn As New SqlConnection(Me.Conexao)
            cmd.Connection = cnn
            cnn.Open()
            Return cmd.ExecuteScalar()
        End Using
    End Function





    Protected Function ExecuteReaderReturnTable(ByVal cmd As SqlCommand) As DataTable
        Using cnn As New SqlConnection(Me.Conexao)
            cmd.Connection = cnn
            cnn.Open()
            Dim tb As New DataTable()
            Using reader As SqlDataReader = cmd.ExecuteReader()
                tb.Load(reader)
            End Using
            Return tb
        End Using
    End Function

    Protected Sub ExecuteNonQuery(ByVal cmd As SqlCommand)
        Using cnn As New SqlConnection(Me.Conexao)
            cmd.Connection = cnn
            cnn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Sub


    Protected Function ExecuteAtomicScalar(ByVal cmd As SqlCommand) As Integer
        CreateTransaction()
        cmd.Connection = _transactionConnection
        Dim result As Integer = cmd.ExecuteScalar()
        Return result
    End Function

    Protected Sub ExecuteAtomicNonQuery(ByVal cmd As SqlCommand)
        CreateTransaction()
        cmd.ExecuteNonQuery()
    End Sub

    Protected Sub CommitAtomicCommands()

        Try
            Me._transactionConnection.Open()
            Me._transaction.Commit()
        Catch ex As Exception

        Finally
            Me._transaction.Dispose()
            Me._transactionConnection.Close()
            Me._transactionConnection.Dispose()
        End Try





    End Sub



    Protected Sub PopulateCmdWithContatoValues(ByRef cmd As SqlCommand, ByVal contato As Model.Pessoa)
        AddParameterToCmd(cmd, "@Bairro", contato.Bairro)
        AddParameterToCmd(cmd, "@Cep", contato.CEP)
        AddParameterToCmd(cmd, "@Cidade", contato.Cidade)
        AddParameterToCmd(cmd, "@Email", contato.Email)
        AddParameterToCmd(cmd, "@Endereco", contato.Endereco)
        AddParameterToCmd(cmd, "@Estado", contato.Estado)
        AddParameterToCmd(cmd, "@Messenger", contato.Messenger)
        AddParameterToCmd(cmd, "@Telefone1", contato.Telefone1)
        AddParameterToCmd(cmd, "@Telefone2", contato.Telefone2)
        AddParameterToCmd(cmd, "@Observacoes", contato.Observacoes)
    End Sub

    Protected Function GetContatoStringUpdate() As String
        Return "  Bairro=@Bairro,  Cep=@Cep,  Cidade=@Cidade,  Email=@Email,  Endereco=@Endereco,  Estado=@Estado,  Messenger=@Messenger,  Telefone1 = @Telefone1,  Telefone2 = @Telefone2, Observacoes = @Observacoes "
    End Function
    Protected Function GetContatoStringInsert() As String
        Return "  Bairro=@Bairro,  Cep=@Cep,  Cidade=@Cidade,  Email=@Email,  Endereco=@Endereco,  Estado=@Estado,  Messenger=@Messenger,  Telefone1 = @Telefone1,  Telefone2=@Telefone2 "
    End Function



    Protected Sub BuildContatoWithDataRow(ByVal row As DataRow, ByRef pessoa As Pessoa)
        pessoa.Bairro = Convert.ToString(row("Bairro"))
        pessoa.CEP = Convert.ToString(row("Cep"))
        pessoa.Cidade = Convert.ToString(row("Cidade"))
        pessoa.Endereco = Convert.ToString(row("Endereco"))
        pessoa.Estado = Convert.ToString(row("Estado"))
        pessoa.Messenger = Convert.ToString(row("Messenger"))
        pessoa.Telefone1 = Convert.ToString(row("Telefone1"))
        pessoa.Telefone2 = Convert.ToString(row("Telefone2"))
        pessoa.Email = Convert.ToString(row("Email"))
        pessoa.Observacoes = Convert.ToString(row("Observacoes"))

    End Sub
    Protected Function GetStringFromRow(ByVal row As DataRow, ByVal columnName As String) As String
        Return Convert.ToString(row(columnName))
    End Function

    Protected Function GetIntFromRow(ByVal row As DataRow, ByVal columnName As String) As Integer
        Return Convert.ToInt32(row(columnName))
    End Function



    Private Sub CreateTransaction()
        If Me._transaction Is Nothing Then
            _transactionConnection = New SqlConnection(Me.Conexao)
            _transactionConnection.Open()
            _transaction = _transactionConnection.BeginTransaction("DainfoAtomicCommands")
        End If
    End Sub


End Class
